<?php
class Fees_db extends CI_Model {
    function __construct() {
        parent::__construct();
    }
    
    function get_feetype_list() {
        $query = $this->db->get('feetype');
        $result = $query->result_array();
        return $result;
    }
    
    function save_receipt($data,$pk='') {
        $save_result = array();
        
        if ($pk=='') {
    		$this->db->insert('receipt', $data);
    		$save_result['insert_id'] = $this->db->insert_id();
            if($save_result['insert_id'] == 0 || $save_result['insert_id'] == false) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		} 
        /*
        else {
    		$this->db->where('receipt_no', $pk);
    		$this->db->update('receipt', $data);
            //print_object($this->db->last_query());exit;
            $save_result['affected_rows'] = $this->db->affected_rows();
            if($save_result['affected_rows'] == -1 ) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		}
        */   
        return $save_result;
    }
    
    function save_receipt_item($data,$pk='') {
        $save_result = array();        
        if ($pk=='') {
    		$this->db->insert('receiptitem', $data);
    		$save_result['insert_id'] = $this->db->insert_id();
            if($save_result['insert_id'] == 0 || $save_result['insert_id'] == false) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		}  
        return $save_result;
    }
    
    function get_receipt($receipt_no) {
        $this->db->from('receipt rc');
        $this->db->join('student st','rc.receipt_regno = st.reg_no','left');
        $this->db->join('class cls','st.student_class = cls.class_id','left');
        $this->db->join('division dv','st.student_division = dv.division_id','left');
        $this->db->join('term tr','rc.receipt_term_id = tr.term_id','left');
        $this->db->where(array("receipt_no"=>$receipt_no));
        $query = $this->db->get();
        $result = $query->result_array();
        $receipt = null;
        if(count($result) > 0) {
            $receipt = $result[0];
            $receipt["items"] = $this->get_receipt_items($receipt_no);
        }
        //print_object($receipt); exit;
        if($receipt["receipt_is_general"]=='1') {
            $receipt['student_name'] = $receipt['receipt_student_name'];
        } 
        if($receipt['receipt_term_id'] != '0' && $receipt['receipt_term_year'] != '0000') {
            $term_data = get_year_term($receipt['term_start'], $receipt['term_end'], $receipt['receipt_term_year']);
            //print_object($term_data); exit;
            $receipt['receipt_term'] = $receipt['receipt_term_id'].'-'.$receipt['receipt_term_year'];
            $receipt['receipt_term_label'] = $term_data['term_label'];
        } else {
            $receipt['receipt_term'] = '';
            $receipt['receipt_term_label'] = '';
        }       
        return $receipt;
    }
    
    function get_receipt_items($receipt_no)   {
        $this->db->from('receiptitem ri');
        $this->db->join('feetype ft','ri.ri_feetype = ft.feetype_id','left');
        $this->db->where(array("ri_receiptno"=>$receipt_no));
        $query = $this->db->get();
        $result = $query->result_array();
        return $result;
    }
    
    function get_meta() {
        $meta = array(
            "table"=>"receipt",
            "fields" => array(
                "receipt_date" => array("label"=>"Date","type"=>"format",'function'=>'get_date_format','param'=>'receipt_date'),
                "receipt_term_id" => array("label"=>"Term","type"=>"format",'function'=>'get_term_label','param'=>'term_start|term_end'),
                'receipt_term_year'=>array('label'=>'Year','type'=>'string','visible'=>true),
                'receipt_regno'=>array('label'=>'Reg. No.','type'=>'link','visible'=>true, 'key'=>'receipt_regno', 'href'=>'/student/viewstudent'),
                'student_name'=>array('label'=>'Name','type'=>'link','visible'=>true, 'key'=>'receipt_regno', 'href'=>'/student/viewstudent'),
                'class_label'=>array('label'=>'Class','type'=>'string','visible'=>true),
                'division_label'=>array('label'=>'Division','type'=>'string','visible'=>true),
                'receipt_total'=>array('label'=>'Total','type'=>'string','visible'=>true), 
                'receipt_discount'=>array('label'=>'Discount','type'=>'string','visible'=>true),  
                'receipt_net'=>array('label'=>'Net','type'=>'string','visible'=>true)
            )
        );
        
        return $meta;
    }
    
    function get_receipt_filter_count($filterdata='',$include_deleted=false) {
        $this->db->from('receipt rc');
        $this->db->join('student st','rc.receipt_regno = st.reg_no','left');
        $this->db->join('class cls','st.student_class = cls.class_id','left');
        $this->db->join('division dv','st.student_division = dv.division_id','left');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = (isset($fitem['col'])) ? $fitem['col'] : $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else {
                            $check = "";
                            if(isset($fitem['check'])) {
                                if($fitem['type'] == 'date' && $fitem['check'] == '<=') {
                                    $value = my_add_date($value,1);
                                }
                                $check = " ".$fitem['check'];
                            }
                            $this->db->where($tbl.'.'.$field.$check,$value);
                        }
                    }
                }
            }
        }
        
        $this->db->order_by("rc.receipt_no", "desc"); 
        
        return $this->db->count_all_results();
    }
    
    function get_receipt_filter_list($limit=0,$start=0,$filterdata='',$include_deleted=false) {
        $this->db->select('rc.*, st.*, cls.*, dv.*, tr.*, SUM(ri.ri_amount) as receipt_total, ((SUM(ri.ri_amount))-(rc.receipt_discount)) as receipt_net ' );
        $this->db->from('receipt rc');
        $this->db->join('student st','rc.receipt_regno = st.reg_no','left');
        $this->db->join('class cls','st.student_class = cls.class_id','left');
        $this->db->join('division dv','st.student_division = dv.division_id','left');
        $this->db->join('term tr','rc.receipt_term_id = tr.term_id','left');
        $this->db->join('receiptitem ri','rc.receipt_no = ri.ri_receiptno','left');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = (isset($fitem['col'])) ? $fitem['col'] : $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else {
                            $check = "";
                            if(isset($fitem['check'])) {
                                if($fitem['type'] == 'date' && $fitem['check'] == '<=') {
                                    $value = my_add_date($value,1);
                                }
                                $check = " ".$fitem['check'];
                            }
                            $this->db->where($tbl.'.'.$field.$check,$value);
                        }
                    }
                }
            }
        }
        
        $this->db->group_by("rc.receipt_no"); 
        
        $this->db->order_by("rc.receipt_no", "desc"); 
        
        $this->db->limit($limit, $start); 
        $query = $this->db->get();
        //print_object($this->db->last_query()); exit;
        $receipts = $query->result_array();
        
        if(count($receipts) > 0) {
            foreach($receipts as $key => $receipt) {
                if($receipt["receipt_is_general"]=='1') {
                    $receipt['student_name'] = $receipt['receipt_student_name'];
                    $receipts[$key] = $receipt;
                } 
            }
        }
         
        return $receipts;
    }
    
    function build_receipt_dg_data($fiterdata='') {
        $result['dg_title'] = "Receipt List";
        if($this->input->post('show_all')) {
            $result['show_all'] = true;
        } else {
            //$result['show_all'] = false;
            $result['current_page'] = ($this->input->post('page')) ? $this->input->post('page') : 1;
            $result['rows_per_page'] = ($this->config->item('per_page')) ? $this->config->item('per_page') : 10;
        }
        
        if($fiterdata=='')
            $result['total'] = $this->get_receipt_filter_count();
        else
            $result['total'] = $this->get_receipt_filter_count($fiterdata);
        
        if($result['show_all']) {
            $result['current_page'] = 1;
            $result['rows_per_page'] = ($result['total']==0)?100:$result['total'];
        }    
        
        $result['pk'] = 'receipt_no';
        $result['itemname'] = 'student_name';
        
        if(is_array($fiterdata['cols']) && count($fiterdata['cols']) > 0) {
            $dgcols = array(
                'receipt_no'=>array('label'=>'Receipt No.','type'=>'link','visible'=>true, 'href'=>'/fees/viewreceipt')
            );
            foreach($fiterdata['cols'] as $col) {
                $dgcol = add_dg_col($col,$fiterdata['meta']['fields'][$col],$dgcols);
            }
            $result['dgcolumns'] = $dgcols;
        } else {
            $result['dgcolumns'] = array(
                'receipt_no'=>array('label'=>'Receipt No.','type'=>'link','visible'=>true, 'href'=>'/fees/viewreceipt'),
                'receipt_date'=>array('label'=>'Date','type'=>'format','function'=>'get_date_format','param'=>'receipt_date','visible'=>true),
                'receipt_term_id'=>array('label'=>'Term','type'=>'format', 'function'=>'get_term_label', 'param'=>'term_start|term_end', 'visible'=>true),
                'receipt_term_year'=>array('label'=>'Year','type'=>'string','visible'=>true),
                'receipt_regno'=>array('label'=>'Reg. No.','type'=>'link','visible'=>true, 'key'=>'receipt_regno', 'href'=>'/student/viewstudent'),
                'student_name'=>array('label'=>'Name','type'=>'link','visible'=>true, 'key'=>'receipt_regno', 'href'=>'/student/viewstudent'),
                'class_label'=>array('label'=>'Class','type'=>'string','visible'=>true),
                'division_label'=>array('label'=>'Division','type'=>'string','visible'=>true),
                'receipt_total'=>array('label'=>'Total','type'=>'string','visible'=>true), 
                'receipt_discount'=>array('label'=>'Discount','type'=>'string','visible'=>true),  
                'receipt_net'=>array('label'=>'Net','type'=>'string','visible'=>true)     
            );
        }
        
        $result['actions'] = array(
        'view'=>'/fees/viewreceipt',
        'page'=>'/fees/searchreceipt'
        );
        
        $offset = $result['rows_per_page']*($result['current_page']-1);
        $result['pages_total'] =  ceil($result['total']/$result['rows_per_page']);
        if($fiterdata=='')
            $result["dgitems"] = $this->get_receipt_filter_list($result['rows_per_page'], $offset, '');
        else
            $result["dgitems"] = $this->get_receipt_filter_list($result['rows_per_page'], $offset, $fiterdata);
        return $result;
    }
    
    function get_feereport_list($filterdata='') {
        $this->db->select('rc.*, st.*, cls.*, dv.*, tr.*, GROUP_CONCAT(ri.ri_feetype SEPARATOR "|")  as receipt_fee_items, GROUP_CONCAT(ri.ri_amount SEPARATOR "|") as receipt_fee_amounts, SUM(ri.ri_amount) as receipt_total, ((SUM(ri.ri_amount))-(rc.receipt_discount)) as receipt_net ' );
        $this->db->from('receipt rc');
        $this->db->join('student st','rc.receipt_regno = st.reg_no','left');
        $this->db->join('class cls','st.student_class = cls.class_id','left');
        $this->db->join('division dv','st.student_division = dv.division_id','left');
        $this->db->join('term tr','rc.receipt_term_id = tr.term_id','left');
        $this->db->join('receiptitem ri','rc.receipt_no = ri.ri_receiptno','left');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = (isset($fitem['col'])) ? $fitem['col'] : $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else {
                            $check = "";
                            if(isset($fitem['check'])) {
                                if($fitem['type'] == 'date' && $fitem['check'] == '<=') {
                                    $value = my_add_date($value,1);
                                }
                                $check = " ".$fitem['check'];
                            }
                            $this->db->where($tbl.'.'.$field.$check,$value);
                        }
                    }
                }
            }
        }
        
        $this->db->group_by("rc.receipt_no"); 
        
        $this->db->order_by("rc.receipt_no", "desc"); 
         
        $query = $this->db->get();
        //print_object($this->db->last_query());
        $receipts = $query->result_array();
        
        $feereport = array();
        
        if(count($receipts) > 0) {
            foreach($receipts as $key => $receipt) {
                $fritem = array();
                $fritem['receipt_no'] = $receipt['receipt_no'];
                $fritem['receipt_date'] = $receipt['receipt_date'];
                $fritem['receipt_term_id'] = $receipt['receipt_term_id'];
                $fritem['term_start'] = $receipt['term_start'];
                $fritem['term_end'] = $receipt['term_end'];
                $fritem['receipt_term_year'] = $receipt['receipt_term_year'];
                $fritem['class_label'] = $receipt['class_label']; 
                $fritem['receipt_regno'] = $receipt['receipt_regno'];
                $fritem['receipt_discount'] = $receipt['receipt_discount'];
                $fritem['student_name'] = $receipt['student_name'];  
                $fritem['receipt_comment'] = $receipt['receipt_comment'];              
                if($receipt["receipt_is_general"]=='1') {
                    $fritem['student_name'] = $receipt['receipt_student_name'];
                }
                if($receipt['receipt_fee_items']) {
                    $receipt_fee_items = explode("|",$receipt['receipt_fee_items']);
                    $receipt_fee_amounts = explode("|",$receipt['receipt_fee_amounts']);
                    $i = 0;
                    foreach($receipt_fee_items as $fee_item) {
                        $fritem["fee_item_$fee_item"] = $receipt_fee_amounts[$i];
                        $i++;
                    }
                }
                $fritem['receipt_total'] = $receipt['receipt_total']; 
                $fritem['receipt_net'] = $receipt['receipt_net']; 
                
                $feereport[$key] = $fritem;
            }
        }
        //print_object($feereport); 
        return $feereport;
    }
    
    function build_feereport_dg_data($fiterdata='') {
        $result['dg_title'] = "Fee Report";
        
        $result['pk'] = 'receipt_no';
        $result['itemname'] = 'student_name';
        $result['report'] = true;
        $result['total'] = true;
        $result['total_span'] = 6;
        
        $result['dgcolumns'] = array(
            'receipt_no'=>array('label'=>'Receipt No.','type'=>'link','visible'=>true, 'href'=>'/fees/viewreceipt'),
            'receipt_date'=>array('label'=>'Date','type'=>'format','function'=>'get_date_format','param'=>'receipt_date','visible'=>true),
            'class_label'=>array('label'=>'Class','type'=>'string','visible'=>true),
            'receipt_regno'=>array('label'=>'Reg. No.','type'=>'link','visible'=>true, 'key'=>'receipt_regno', 'href'=>'/student/viewstudent'),
            'student_name'=>array('label'=>'Name','type'=>'string','visible'=>true)
        );
        
        $fee_type_list = $this->get_feetype_list();
        foreach($fee_type_list as $fee_type) {
            $fee_item = $fee_type['feetype_id'];
            $result['dgcolumns']["fee_item_$fee_item"] = array('label'=>$fee_type['feetype_label'],'type'=>'string','visible'=>true, 'sum'=>true); 
        }
        $result['dgcolumns']['receipt_total'] = array('label'=>'Total','type'=>'string','visible'=>true, 'sum'=>true); 
        $result['dgcolumns']['receipt_discount'] = array('label'=>'Discount','type'=>'string','visible'=>true, 'sum'=>true); 
        $result['dgcolumns']['receipt_net'] = array('label'=>'Net','type'=>'string','visible'=>true, 'sum'=>true);   
        
        $result['actions'] = array();
        
        
        $result["dgitems"] = $this->get_feereport_list($fiterdata);
            
        //print_object($result);
                
        return $result;
    }
    
    function format_tution_fee($feedata) {
        $result = array();
        
        foreach($feedata as $fee) {
            $result[$fee['tf_termid']."_".$fee['tf_classid']] = $fee['tf_amount'];
        }
        //print_object($feedata);
        return $result;
    }
    
    function get_tution_fee() {
        $this->db->from('tutionfee');
        $query = $this->db->get();
        $fees = $this->format_tution_fee($query->result_array());        
        return $fees;
    }
    
    function save_tutionfee($newdata, $olddata, $user, $time) {
        foreach($newdata as $fee) {
            $termid = $fee['termid'];
            $classid = $fee['classid'];
            $amount = $fee['amount'];
            
            $key = $termid."_".$classid;
            if(array_key_exists($key,$olddata) ) {   // Update record
                if($olddata[$key] != $amount) { // Update only if amount changed.
                    $data = array('tf_amount'=>$amount);
                    $this->db->where('tf_termid', $termid);
                    $this->db->where('tf_classid', $classid);
                    $this->db->update('tutionfee', $data);                    
                }
            } else {    //Insert new record
                $data = array(
                    'tf_classid'=>$classid,
                    'tf_termid'=>$termid,
                    'tf_amount'=>$amount,
                    'createdby'=>$user,
                    'createdon'=>$time
                );
                $this->db->insert('tutionfee', $data); 
            }
        }

        $save_result['result'] = 'S';   
        return $save_result;
    }
    
    function save_transportfee($newdata, $olddata) {
        $oldlist = array();
        foreach($olddata as $oldfee) {
            $oldlist[$oldfee['area_id']] = $oldfee['area_transport_fee'];
        }
        foreach($newdata as $fee) {
            $areaid = $fee['areaid'];
            $amount = $fee['amount'];
            
            if(array_key_exists($areaid,$oldlist) ) {   // Update record
                if($oldlist[$areaid] != $amount) { // Update only if amount changed.
                    $data = array('area_transport_fee'=>$amount);
                    $this->db->where('area_id', $areaid); 
                    $this->db->update('area', $data);                    
                }
            }
        }

        $save_result['result'] = 'S';   
        return $save_result;
    }
    
    function get_misc_list($orderby="") {
        if($orderby!="") {
            $this->db->order_by($orderby);
        }
        $this->db->where('feetype_isset', 1); 
        $query = $this->db->get('feetype');
        $result = $query->result_array();
        return $result;
    } 
    
    function save_miscfee($newdata, $olddata) {
        $oldlist = array();
        foreach($olddata as $oldfee) {
            $oldlist[$oldfee['feetype_id']] = $oldfee['feetype_fee'];
        }
        foreach($newdata as $fee) {
            $feetype_id = $fee['feetype_id'];
            $amount = $fee['amount'];
            
            if(array_key_exists($feetype_id,$oldlist) ) {   // Update record
                if($oldlist[$feetype_id] != $amount) { // Update only if amount changed.
                    $data = array('feetype_fee'=>$amount);
                    $this->db->where('feetype_id', $feetype_id); 
                    $this->db->update('feetype', $data);                    
                }
            }
        }

        $save_result['result'] = 'S';   
        return $save_result;
    } 
}